package com.alvin.mobi.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.alvin.mobi.domain.Department;
import com.alvin.mobi.domain.DepartmentType;
import com.alvin.mobi.domain.Locus;
import com.alvin.mobi.domain.Merchandise;
import com.alvin.mobi.domain.QrCodeType;

@Service(value="merchandiseService")
public class MerchandiseService {
	
	private NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
	
	public Merchandise retrieveGood(String qrCode){
		String sql = "SELECT DISTINCT(q.ID) as ID, b.NAME as BRAND, g.PRODUCE_DATE, m.NAME, m.PRODUCE_PLACE, m.CODE, CONCAT(CONVERT(m.SHELFLIFE,char), '个月') as SHELFLIFE, CONCAT(CONVERT(m.WEIGHT,char),'克*',CONVERT(m.NUM_OF_BOX,char),CONVERT(m.PACKAGEING,char),'/件') as SPCIFICATION, CONCAT(CONVERT(m.PRICE,char),'元') as PRICE FROM GOODS g LEFT JOIN MARCHANDISE m ON g.MARCHANDISE_ID = m.ID LEFT JOIN QR_GOODS_CODE q ON g.QR_CODE_ID = q.ID LEFT JOIN BRAND b on b.ID = m.BRAND_ID WHERE q.W_CODE = ?";
		try {
			Merchandise merchandise = (Merchandise) jdbcTemplate.getJdbcOperations()
					.queryForObject(sql, new Object[] { qrCode },
							new MerchandiseRowMapper());
			merchandise.setQrType(QrCodeType.QR_GOODS);
			merchandise.setLocus(this.retrieveLocus(qrCode, QrCodeType.QR_GOODS));
			return merchandise;
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}		
	}
	
	public Merchandise retrieveBox(String qrCode){
		String sql = "SELECT DISTINCT(q.ID) as ID, b.NAME as BRAND, g.PRODUCE_DATE, m.NAME, m.PRODUCE_PLACE, m.CODE, CONCAT(CONVERT(m.SHELFLIFE,char), '个月') as SHELFLIFE, CONCAT(CONVERT(m.WEIGHT,char),'克*',CONVERT(m.NUM_OF_BOX,char),CONVERT(m.PACKAGEING,char),'/件') as SPCIFICATION, CONCAT(CONVERT(m.PRICE,char),'元') as PRICE FROM GOODS g LEFT JOIN MARCHANDISE m ON g.MARCHANDISE_ID = m.ID LEFT JOIN QR_box_CODE q ON g.QR_CODE_ID = q.ID LEFT JOIN BRAND b on b.ID = m.BRAND_ID WHERE q.W_CODE = ?";
		try {
			Merchandise merchandise = (Merchandise) jdbcTemplate.getJdbcOperations()
					.queryForObject(sql, new Object[] { qrCode },
							new MerchandiseRowMapper());
			merchandise.setQrType(QrCodeType.QR_BOX);
			merchandise.setLocus(this.retrieveLocus(qrCode, QrCodeType.QR_BOX));
			return merchandise;
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}			
	}
	
	public List<Locus> retrieveLocus(String qrCode, QrCodeType qrCodeType){
		String qrTable = QrCodeType.QR_GOODS.equals(qrCodeType)?"QR_GOODS_CODE":"QR_BOX_CODE";
		String sql = "SELECT A.INSERT_TIME AS ARRIVE_TIME,T.ID AS DEPARTMENT_ID,B.NAME AS DEPARTMENT_NAME,B.ADDR_AREA AS DEPARTMENT_AREA,T.ID AS TYPE_ID,A.QR_CODE_ID FROM GOODS A,DEPARTMENT B,DEPARTMENT_TYPE T WHERE QR_CODE_ID=(SELECT ID FROM "+qrTable+" WHERE W_CODE=?) AND A.DEPARTMENT_ID=B.ID AND B.TYPE_ID = T.ID ORDER BY ARRIVE_TIME";
		List<Locus> locuses = jdbcTemplate.getJdbcOperations().query(sql, new Object[] {qrCode}, new LocusRowMapper()); 
		return locuses;
	}
	
    static class MerchandiseRowMapper implements RowMapper<Merchandise> {

        public Merchandise mapRow(ResultSet rs, int rowNum) throws SQLException {
        	Merchandise merchandise = new Merchandise();
        	merchandise.setId(rs.getInt("ID")); // qrCode ID
        	merchandise.setBrand(rs.getString("BRAND"));
        	merchandise.setName(rs.getString("NAME"));
        	merchandise.setProductPlace(rs.getString("PRODUCE_PLACE"));
        	merchandise.setCode(rs.getString("CODE"));
        	merchandise.setPrice(rs.getString("PRICE"));
        	merchandise.setProductDate(rs.getDate("PRODUCE_DATE"));
        	merchandise.setShelflife(rs.getString("SHELFLIFE"));
        	merchandise.setSpcification(rs.getString("SPCIFICATION"));
        	return merchandise;
        }
    }
    
    static class LocusRowMapper implements RowMapper<Locus> {
    	public Locus mapRow(ResultSet rs, int rowNum) throws SQLException {
    		Locus locus = new Locus();
    		Timestamp timestamp = rs.getTimestamp("ARRIVE_TIME");
    		Date arrivaDate = new java.util.Date(timestamp.getTime()); 
    		locus.setArriveDate(arrivaDate);
    		Department department = new Department();
    		department.setDepartmentType(DepartmentType.valueOf(rs.getInt("TYPE_ID")));
    		department.setId(rs.getInt("DEPARTMENT_ID"));
    		department.setName(rs.getString("DEPARTMENT_NAME"));
    		department.setArea(rs.getString("DEPARTMENT_AREA"));
    		locus.setDepartment(department);
    		return locus;
    	}
    }
}
